package wjx.database.jdbc;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

import javax.swing.JOptionPane;

import wjx.database.bean.StringUtil;

public class JDBCUtils {
	static private String DRIVER;
	static private String URL;

	// database auth
	static private String USER;
	static private String PASSWORD;
	static private Connection CONN;
	static public PreparedStatement ps = null;
	static public ResultSet rs = null;

	static {
		// 加载资源文件到输入流中
		InputStream is = JDBCUtils.class.getClassLoader().getResourceAsStream("db.properties");
		// 创建Properties类型对象加载流文件
		Properties p = new Properties();
		try {
			p.load(is);
			DRIVER = p.getProperty("driver");
			URL = p.getProperty("url");
			USER = p.getProperty("username");
			PASSWORD = p.getProperty("password");
			// 加载MySQL驱动
			Class.forName(DRIVER);
			// 建立数据库连接
			CONN = JDBCUtils.getConection();

		} catch (Exception e) {
			e.printStackTrace();
		}

	}

	// 获得连接对象的方法
	public static Connection getConection() throws SQLException {
		return DriverManager.getConnection(URL, USER, PASSWORD);
	}

	// 释放资源
	public void close() {
		try {
			if (rs != null) {
				rs.close();
				rs = null;
			}
			if (ps != null) {
				ps.close();
				ps = null;
			}
			if (CONN != null) {
				CONN.close();
				CONN = null;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	// SQL语句

	// update
	// return 受影响的条数
	public static int update(String sql) throws SQLException {
//		con = getConnection() ;
		int res = 0;
		ps = CONN.prepareStatement(sql);
		res = ps.executeUpdate();

		return res;
	}

	// create
	// return 受影响的条数
	public static int create(String sql) throws SQLException{
		int res = 0;
		ps = CONN.prepareStatement(sql);
		res = ps.executeUpdate();
		return res;
	}

	// query
	// return 结果集
	public static ResultSet query(String sql) throws SQLException {
		ps = CONN.prepareStatement(sql);
		rs = ps.executeQuery();
		return rs;
	}

	// 获取数据库下所有表名
	public static List<String> GetTableNames() {
		List<String> tableNames = new ArrayList<>();
		try {
//			CONN = getConection() ;
			DatabaseMetaData db = CONN.getMetaData();
			System.out.print(CONN.isValid(0));
			rs = db.getTables(null, "databaseManager", "%", new String[] { "TABLE" });
//			System.out.println("000");
			while (rs.next()) {
//				System.out.println(rs.getString("TABLE_NAME")) ;
				tableNames.add(rs.getString("TABLE_NAME"));

			}
		} catch (Exception e) {
			JOptionPane.showMessageDialog(null, "未找到！");
		}
		return tableNames;
	}

	// 获取某表的列名
	public static List<String> GetTableCol(Object name) {
		List<String> tableNames = new ArrayList<>();
		try {
			DatabaseMetaData db = CONN.getMetaData();
			rs = db.getColumns(null, "databaseManager", name.toString(), "%");
			while (rs.next()) {
				tableNames.add(rs.getString("COLUMN_NAME"));
			}
		} catch (Exception e) {
			JOptionPane.showMessageDialog(null, "未找到！");
		}
		return tableNames;
	}

	// 获取某表的列名和数据类型
	public static List<String> GetTableColAnd(Object name) {
		List<String> tableNames = new ArrayList<>();
		try {
			DatabaseMetaData db = CONN.getMetaData();
			rs = db.getColumns(null, "databaseManager", name.toString(), "%");
			while (rs.next()) {
				tableNames.add(rs.getString("COLUMN_NAME"));
				tableNames.add(rs.getString("TYPE_NAME"));
			}
		} catch (Exception e) {
			JOptionPane.showMessageDialog(null, "未找到！");
		}
		return tableNames;
	}
	// 找到某表的加密列名称
	public static String GetTableRemarks(Object name) {
		int index = -1 ;
		String tbN = "";
		try {
			DatabaseMetaData db = CONN.getMetaData();
			rs = db.getColumns(null, "databaseManager", name.toString(), "%");
			while (rs.next()) {
				tbN = rs.getString("COLUMN_NAME");
				String s = rs.getString("REMARKS");
				if(StringUtil.isNotEmpty(s)){
					if( s.equals("code"))
						return tbN ;
				}
				
			}
		} catch (Exception e) {
			JOptionPane.showMessageDialog(null, "未找到！");
		}
		return "";
	}
	

	// 获取某表的主键
	public static List<String> GetPriKey(String name) {
		List<String> tableNames = new ArrayList<>();
		try {
			DatabaseMetaData db = CONN.getMetaData();
			rs = db.getPrimaryKeys(null, "databaseManager", name);
			while (rs.next()) {
				tableNames.add(rs.getString(4));
			}
		} catch (Exception e) {
			JOptionPane.showMessageDialog(null, "未找到！");
		}
		return tableNames;
	}
}
